package com.boss.salary.dao.impl;

import com.boss.common.util.DateUtils;
import com.boss.framework.dao.BaseDaoImpl;
import com.boss.salary.constant.TableName;
import com.boss.salary.dao.ISalaryMonthEndSubmitDao;
import com.boss.salary.vo.SalaryMonthEndVO;
import com.boss.sys.utils.SysUtils;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.stereotype.Repository;
import org.springframework.util.CollectionUtils;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

@Repository
public class SalaryMonthEndSubmitDaoImpl extends BaseDaoImpl implements ISalaryMonthEndSubmitDao {
   @Override
    public void batchSaveSalaryMonthSubmit(List<SalaryMonthEndVO> salaryMonthEndVOList) throws NoSuchMethodException, InvocationTargetException, IllegalAccessException {
        List<Object[]> paramList = new ArrayList<>(salaryMonthEndVOList.size());
        Class<?> clazz = SalaryMonthEndVO.class;
        for(SalaryMonthEndVO salaryMonthEndVO : salaryMonthEndVOList){
            Object[] paramArr = {
                    SysUtils.generateGuid(), String.valueOf(DateUtils.getCurrentTimeStamp()), String.valueOf(DateUtils.getCurrentTimeStamp()),
                    salaryMonthEndVO.getAgencyId(), salaryMonthEndVO.getPersonId(), salaryMonthEndVO.getMofDivCode(),salaryMonthEndVO.getIsThirteen()
            };
            List<Object> params = new ArrayList<>(Arrays.asList(paramArr));
            for(int i = 1; i < 151; i++){
                Method getMethod = clazz.getDeclaredMethod("getS"+i);
                params.add(getMethod.invoke(salaryMonthEndVO));
            }
            paramList.add(params.toArray());
        }
        StringBuilder sb = new StringBuilder();
        sb.append(" merge into ").append(TableName.SALARY_MONTH_END_SUBMIT)
                .append(" a using " )
                .append(" (select ? id, ? create_time, ? update_time, ? agency_id, ? person_id,? mof_div_code, ? is_thirteen, ? ")
                .append(" s1, ? s2, ? s3, ? s4, ? s5, ? s6, ? s7, ? s8, ? s9, ? s10, ? s11, ? s12, ? s13, ? s14, ? s15, ? s16, ? s17, ? s18, ? s19, ? s20, ?  ")
                .append(" s21, ? s22, ? s23, ? s24, ? s25, ? s26, ? s27, ? s28, ? s29, ? s30, ? s31, ? s32, ? s33, ? s34, ? s35, ? s36, ? s37, ? s38, ? s39, ? s40, ? ")
                .append(" s41, ? s42, ? s43, ? s44, ? s45, ? s46, ? s47, ? s48, ? s49, ? s50, ? s51, ? s52, ? s53, ? s54, ? s55, ? s56, ? s57, ? s58, ? s59, ? s60, ? ")
                .append(" s61, ? s62, ? s63, ? s64, ? s65, ? s66, ? s67, ? s68, ? s69, ? s70, ? s71, ? s72, ? s73, ? s74, ? s75, ? s76, ? s77, ? s78, ? s79, ? s80, ? ")
                .append(" s81, ? s82, ? s83, ? s84, ? s85, ? s86, ? s87, ? s88, ? s89, ? s90, ? s91, ? s92, ? s93, ? s94, ? s95, ? s96, ? s97, ? s98, ? s99, ? s100, ? ")
                .append(" s101, ? s102, ? s103, ? s104, ? s105, ? s106, ? s107, ? s108, ? s109, ? s110, ? s111, ? s112, ? s113, ? s114, ? s115, ? s116, ? s117, ? s118, ? s119, ? s120, ? ")
                .append(" s121, ? s122, ? s123, ? s124, ? s125, ? s126, ? s127, ? s128, ? s129, ? s130, ? s131, ? s132, ? s133, ? s134, ? s135, ? s136, ? s137, ? s138, ? s139, ? s140, ? ")
                .append(" s141, ? s142, ? s143, ? s144, ? s145, ? s146, ? s147, ? s148, ? s149, ? s150 from dual ) b ")
                .append(" on (a.person_id=b.person_id and a.is_thirteen=b.is_thirteen ) " )
                .append(" WHEN MATCHED THEN " )
                .append("  UPDATE SET  a.create_time=b.create_time, a.update_time=b.update_time, a.agency_id=b.agency_id, a.mof_div_code=b.mof_div_code, " )
                .append(" a.s1=b.s1, a.s2=b.s2, a.s3=b.s3,a.s4=b.s4,a.s5=b.s5,a.s6=b.s6, a.s7=b.s7,a.s8=b.s8, a.s9=b.s9,a.s10=b.s10, a.s11=b.s11, a.s12=b.s12,a.s13=b.s13, a.s14=b.s14,a.s15=b.s15, a.s16=b.s16, a.s17=b.s17,a.s18=b.s18, a.s19=b.s19,a.s20=b.s20, ")
                .append(" a.s21=b.s21, a.s22=b.s22, a.s23=b.s23,a.s24=b.s24, a.s25=b.s25,a.s26=b.s26, a.s27=b.s27, a.s28=b.s28,a.s29=b.s29, a.s30=b.s30,a.s31=b.s31, a.s32=b.s32, a.s33=b.s33,a.s34=b.s34, a.s35=b.s35,a.s36=b.s36, a.s37=b.s37, a.s38=b.s38,a.s39=b.s39, a.s40=b.s40, ")
                .append(" a.s41=b.s41, a.s42=b.s42, a.s43=b.s43,a.s44=b.s44, a.s45=b.s45,a.s46=b.s46, a.s47=b.s47, a.s48=b.s48,a.s49=b.s49, a.s50=b.s50,a.s51=b.s51, a.s52=b.s52, a.s53=b.s53,a.s54=b.s54, a.s55=b.s55,a.s56=b.s56, a.s57=b.s57, a.s58=b.s58,a.s59=b.s59, a.s60=b.s60,  ")
                .append(" a.s61=b.s61, a.s62=b.s62, a.s63=b.s63,a.s64=b.s64, a.s65=b.s65,a.s66=b.s66, a.s67=b.s67, a.s68=b.s68,a.s69=b.s69, a.s70=b.s70,a.s71=b.s71, a.s72=b.s72, a.s73=b.s73,a.s74=b.s74, a.s75=b.s75,a.s76=b.s76, a.s77=b.s77, a.s78=b.s78,a.s79=b.s79, a.s80=b.s80,  ")
                .append(" a.s81=b.s81, a.s82=b.s82, a.s83=b.s83,a.s84=b.s84, a.s85=b.s85,a.s86=b.s86, a.s87=b.s87, a.s88=b.s88,a.s89=b.s89, a.s90=b.s90,a.s91=b.s91, a.s92=b.s92, a.s93=b.s93,a.s94=b.s94, a.s95=b.s95,a.s96=b.s96, a.s97=b.s97, a.s98=b.s98,a.s99=b.s99, a.s100=b.s100,  ")
                .append(" a.s101=b.s101, a.s102=b.s102, a.s103=b.s103,a.s104=b.s104, a.s105=b.s105,a.s106=b.s106, a.s107=b.s107, a.s108=b.s108,a.s109=b.s109, a.s110=b.s110,a.s111=b.s111, a.s112=b.s112, a.s113=b.s113,a.s114=b.s114,a.s115=b.s115, a.s116=b.s116,a.s117=b.s117,a.s118=b.s118, a.s119=b.s119, a.s120=b.s120,  ")
                .append(" a.s121=b.s121, a.s122=b.s122, a.s123=b.s123,a.s124=b.s124, a.s125=b.s125,a.s126=b.s126, a.s127=b.s127, a.s128=b.s128,a.s129=b.s129, a.s130=b.s130,a.s131=b.s131, a.s132=b.s132, a.s133=b.s133,a.s134=b.s134, a.s135=b.s135,a.s136=b.s136, a.s137=b.s137, a.s138=b.s138,a.s139=b.s139, a.s140=b.s140,  ")
                .append(" a.s141=b.s141, a.s142=b.s142,a.s143=b.s143, a.s144=b.s144,a.s145=b.s145, a.s146=b.s146, a.s147=b.s147,a.s148=b.s148, a.s149=b.s149,a.s150=b.s150 ")
                .append( " WHEN NOT MATCHED THEN " )
                .append( "   INSERT " )
                .append(" ( a.id, a.create_time, a.update_time, a.agency_id, a.person_id, a.is_thirteen,a.mof_div_code, ")
                .append(" a.s1, a.s2, a.s3, a.s4, a.s5, a.s6, a.s7, a.s8, a.s9, a.s10, a.s11, a.s12, a.s13, a.s14, a.s15, a.s16, a.s17, a.s18, a.s19, a.s20,  ")
                .append(" a.s21, a.s22, a.s23, a.s24, a.s25, a.s26, a.s27, a.s28, a.s29, a.s30, a.s31, a.s32, a.s33, a.s34, a.s35, a.s36, a.s37, a.s38, a.s39, a.s40, ")
                .append(" a.s41, a.s42, a.s43, a.s44, a.s45, a.s46, a.s47, a.s48, a.s49, a.s50, a.s51, a.s52, a.s53, a.s54, a.s55, a.s56, a.s57, a.s58, a.s59, a.s60, ")
                .append(" a.s61, a.s62, a.s63, a.s64, a.s65, a.s66, a.s67, a.s68, a.s69, a.s70, a.s71, a.s72, a.s73, a.s74, a.s75, a.s76, a.s77, a.s78, a.s79, a.s80, ")
                .append(" a.s81, a.s82, a.s83, a.s84, a.s85, a.s86, a.s87, a.s88, a.s89, a.s90, a.s91, a.s92, a.s93, a.s94, a.s95, a.s96, a.s97, a.s98, a.s99, a.s100, ")
                .append(" a.s101,a.s102,a.s103,a.s104,a.s105,a.s106,a.s107,a.s108,a.s109,a.s110,a.s111,a.s112,a.s113,a.s114,a.s115,a.s116,a.s117,a.s118,a.s119,a.s120, ")
                .append(" a.s121,a.s122,a.s123,a.s124,a.s125,a.s126,a.s127,a.s128,a.s129,a.s130,a.s131,a.s132,a.s133,a.s134,a.s135,a.s136,a.s137,a.s138,a.s139,a.s140, ")
                .append(" a.s141,a.s142,a.s143,a.s144,a.s145,a.s146,a.s147,a.s148,a.s149,a.s150 ) ")
                .append( "   VALUES " )
                .append(" ( b.id, b.create_time, b.update_time, b.agency_id, b.person_id, b.is_thirteen,b.mof_div_code, ")
                .append(" b.s1, b.s2, b.s3, b.s4, b.s5, b.s6, b.s7, b.s8, b.s9, b.s10, b.s11, b.s12, b.s13, b.s14, b.s15, b.s16, b.s17, b.s18, b.s19, b.s20,  ")
                .append(" b.s21, b.s22, b.s23, b.s24, b.s25, b.s26, b.s27, b.s28, b.s29, b.s30, b.s31, b.s32, b.s33, b.s34, b.s35, b.s36, b.s37, b.s38, b.s39, b.s40, ")
                .append(" b.s41, b.s42, b.s43, b.s44, b.s45, b.s46, b.s47, b.s48, b.s49, b.s50, b.s51, b.s52, b.s53, b.s54, b.s55, b.s56, b.s57, b.s58, b.s59, b.s60, ")
                .append(" b.s61, b.s62, b.s63, b.s64, b.s65, b.s66, b.s67, b.s68, b.s69, b.s70, b.s71, b.s72, b.s73, b.s74, b.s75, b.s76, b.s77, b.s78, b.s79, b.s80, ")
                .append(" b.s81, b.s82, b.s83, b.s84, b.s85, b.s86, b.s87, b.s88, b.s89, b.s90, b.s91, b.s92, b.s93, b.s94, b.s95, b.s96, b.s97, b.s98, b.s99, b.s100, ")
                .append(" b.s101,b.s102,b.s103,b.s104,b.s105,b.s106,b.s107,b.s108,b.s109,b.s110,b.s111,b.s112,b.s113,b.s114,b.s115,b.s116,b.s117,b.s118,b.s119,b.s120, ")
                .append(" b.s121,b.s122,b.s123,b.s124,b.s125,b.s126,b.s127,b.s128,b.s129,b.s130,b.s131,b.s132,b.s133,b.s134,b.s135,b.s136,b.s137,b.s138,b.s139,b.s140, ")
                .append(" b.s141,b.s142,b.s143,b.s144,b.s145,b.s146,b.s147,b.s148,b.s149,b.s150 ) ");
        super.getJdbcTemplate().batchUpdate(sb.toString(), new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return paramList.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = paramList.get(i);
                for(int a = 0; a <= 156; a++){
                        ps.setObject(a+1,sme[a]);
                }

            }
        });
    }

    @Override
    public void batchPhysicalDeleteSalaryMonthSubmit(String province, List<String> agencyIds) {
        StringBuilder sb = new StringBuilder();
        sb.append(" delete from ").append(TableName.SALARY_MONTH_END_SUBMIT)
                .append(" where ").append(" mof_div_code = ? ");
        if(!CollectionUtils.isEmpty(agencyIds)){
            sb.append(" and ").append(SysUtils.inSql("agency_id", agencyIds));
        }
        super.getjdbcTemplate().update(sb.toString(), province);
    }
    @Override
    public void batchPhysicalDeleteSalaryMonthSubmits(String province, List<String> agencyIds,Integer IsThirteen) {
        StringBuilder sb = new StringBuilder();
        sb.append(" delete from ").append(TableName.SALARY_MONTH_END_SUBMIT)
                .append(" where ").append(" mof_div_code = ? ");
        if(!CollectionUtils.isEmpty(agencyIds)){
            sb.append(" and ").append(SysUtils.inSql("agency_id", agencyIds));
        }
        sb.append(" and is_thirteen=? ");
        super.getjdbcTemplate().update(sb.toString(), province,IsThirteen);
    }
}
